#!/bin/bash

if [ -n "$1" ]; then
data_date=$1
else
  data_date=$(date -d "-1 day" +%F)
fi

DIM_ADS_PLATFORM_SQL="
WITH tmp1 AS
         (
             SELECT
                 id,ad_id,platform_id,create_time,cancel_time
             FROM jtp_ads_warehouse.ods_ads_platform
             WHERE dt='${data_date}'
         ),
     tmp2 AS
         (
             SELECT
                 id, product_id, material_id, group_id, ad_name
             FROM jtp_ads_warehouse.ods_ads
             WHERE dt='${data_date}'
         ),
     tmp3 AS
         (
             SELECT
                 id, name, price, dt
             FROM jtp_ads_warehouse.ods_product
             WHERE dt='${data_date}'
         ),
     tmp4 AS
         (
             SELECT
                 id, platform, platform_alias_zh, dt
             FROM jtp_ads_warehouse.ods_platform_info
             WHERE dt='${data_date}'
         )
INSERT OVERWRITE TABLE jtp_ads_warehouse.dim_ads_platform_info_full PARTITION (dt='${data_date}')
SELECT
    tmp1.id
     ,tmp1.ad_id
     ,tmp2.ad_name
     ,tmp2.group_id
     ,tmp2.product_id
     ,tmp3.name
     ,tmp3.price
     ,tmp2.material_id
     ,tmp1.platform_id
     ,tmp4.platform
     ,tmp4.platform_alias_zh
     ,tmp1.create_time
     ,tmp1.cancel_time
FROM tmp1
         LEFT JOIN tmp2 ON tmp1.ad_id=tmp2.id
         LEFT JOIN tmp3 ON tmp2.product_id=tmp3.id
         LEFT JOIN tmp4 ON tmp1.platform_id=tmp4.id
;
"

TMP_DWD_ADS_EVENT_LOG_DIM_SQL="
INSERT OVERWRITE TABLE jtp_ads_warehouse.tmp_dwd_ads_event_log_dim
SELECT
    t1.event_time,
    t1.event_type,
    t1.ads_id,
    t2.ad_name,
    t2.product_id AS ads_product_id,
    t2.product_name AS ads_product_name,
    t2.product_price AS ads_product_price,
    t2.material_id AS ads_material_id,
    t2.ad_group_id AS ads_group_id,
    t2.platform_id,
    t1.platform_name_en,
    t2.platform_name_zh,
    t1.client_ip,
    t1.client_device_id,
    t1.client_os_type,
    t1.client_user_agent
FROM jtp_ads_warehouse.tmp_dwd_ads_event_log_parse t1
         LEFT JOIN (
    SELECT
        ad_id,
        ad_name,
        ad_group_id,
        product_id,
        product_name,
        product_price,
        material_id,
        platform_id,
        platform_name,
        platform_name_zh
    FROM jtp_ads_warehouse.dim_ads_platform_info_full
    WHERE dt = '${data_date}'
) t2 ON t1.ads_id = t2.ad_id;
"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_ADS_PLATFORM_SQL}${TMP_DWD_ADS_EVENT_LOG_DIM_SQL}"